🔐 Database Privilege Management

WordPress Subdomain Security Configuration Guide

Step 1Database Privilege Revocation & Assignment

When setting up a WordPress subdomain site, it's crucial to follow the principle of least privilege. This means revoking all database privileges from the database user and then assigning only the essential privileges needed for the site to function correctly.

Database Privilege Management Workflow
1. Identify Database Name & Username
↓
2. Revoke All Existing Privileges
↓
3. Grant Only Essential Privileges
↓
4. Flush Privileges & Verify

1.1 Identify Database Credentials

Before modifying privileges, you need to extract the database name and username from your WordPress configuration file. Use the grep command to retrieve this information:

sudo grep DB_NAME /var/www/sub.example.com/public_html/wp-config.php

This command will return the database name. To get the database username, modify the command:

sudo grep DB_USER /var/www/sub.example.com/public_html/wp-config.php
💡 Pro Tip: Press the up arrow key to repeat the previous command and simply change DB_NAME to DB_USER to save time.

1.2 Revoke All Database Privileges

After identifying the credentials, log into MariaDB and revoke all privileges from the user on the specified database. This ensures a clean slate before assigning specific permissions.

sudo mysql -u root -p

Once logged into MariaDB, execute the following command to revoke all privileges:

REVOKE ALL PRIVILEGES ON database_name.* FROM 'database_user'@'localhost';
⚠️ Important: Replace database_name with your actual database name and database_user with your actual username. The asterisk (*) applies the revocation to all tables within the database.

1.3 Grant Essential Privileges

After revoking all privileges, grant only the specific privileges required for WordPress to function properly. The essential privileges are SELECT, INSERT, UPDATE, and DELETE.

GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'database_user'@'localhost';
Privilege Purpose Required for WordPress
SELECT Read data from database tables ✅ Essential
INSERT Add new records to database tables ✅ Essential
UPDATE Modify existing records in database tables ✅ Essential
DELETE Remove records from database tables ✅ Essential
CREATE Create new tables or databases ⚠️ Plugin-dependent
DROP Delete tables or databases ⚠️ Plugin-dependent
ALTER Modify table structure ⚠️ Plugin-dependent
📋 Note: If specific plugins require additional privileges (such as CREATE, DROP, or ALTER), research the plugin's requirements and grant those privileges accordingly. However, for most WordPress installations, the four essential privileges are sufficient.

1.4 Apply Changes and Exit

After granting the necessary privileges, flush the privilege tables to ensure changes take effect immediately:

FLUSH PRIVILEGES;

Exit MariaDB:

exit
✅ Success! Database privileges have been successfully configured following the principle of least privilege.

Step 2File Ownership & Permissions Management

Proper file ownership and permissions are critical for WordPress security. We'll use bash scripts to manage permissions efficiently during site administration and after updates.

2.1 Prepare Update Scripts

Navigate to your bash scripts directory:

cd ~/wp_bash_scripts/

Copy existing scripts for your subdomain site:

cp pre-update_example.wp pre-update_sub.example.com.wp
cp post-update_example.wp post-update_sub.example.com.wp
💡 Tip: You don't need to create new scripts from scratch. Simply copy existing scripts and modify them for the new subdomain site.

2.2 Modify Pre-Update Script

Edit the pre-update script to match your subdomain configuration:

nano pre-update_sub.example.com.wp

Make the following changes:

Save and close the file (Ctrl+X, then Y, then Enter).

2.3 Modify Post-Update Script

Edit the post-update script similarly:

nano post-update_sub.example.com.wp

Make the same changes as the pre-update script:

Save and close the file.

2.4 Understanding Script Impact

Permission Management Comparison
Before Post-Update Script

Files: Read + Write (User & Group)
Directories: Read + Write + Execute (User & Group)
After Post-Update Script

Core Files: Read-only
Core Directories: Read + Execute only
wp-content: Read + Write (maintained)

View current permissions before running scripts:

ls -l /var/www/sub.example.com/public_html/

2.5 Execute Post-Update Script

Run the post-update script to apply hardened permissions:

bash post-update_sub.example.com.wp

Verify the changes:

ls -l /var/www/sub.example.com/public_html/

You'll observe that:

2.6 Execute Pre-Update Script

When performing site administration tasks, run the pre-update script:

bash pre-update_sub.example.com.wp

This restores write permissions temporarily, allowing issue-free site administration.

ls -l /var/www/sub.example.com/public_html/
🔄 Workflow Recommendation:
  • During Setup: Run pre-update script for unrestricted administration
  • After Setup: Run post-update script to harden permissions
  • Before Updates: Run pre-update script
  • After Updates: Run post-update script

Step 3Installation Completion Checklist

At this stage, your subdomain site installation is nearly complete. Here's what has been accomplished:

Security Measure Status Notes
Database Privilege Management ✅ Complete Least privilege principle applied
File Ownership & Permissions ✅ Complete Scripts configured and ready
PHP-FPM Pool Configuration ✅ Complete Isolated user per site
Nginx Server Block ✅ Complete HTTP/2, HTTP/3, SSL configured
SSL Certificate (Wildcard) ✅ Complete Valid for all subdomains
Rate Limiting ✅ Complete wp-login.php and xmlrpc.php protected
Web Application Firewall ⏳ Pending Plugin configuration needed
REST API Restrictions ⏳ Pending Disable for non-logged-in users
Hotlinking Protection 📋 Reference First Site See primary site configuration
Disallow File Modifications 📋 Reference First Site See primary site configuration

3.1 Remaining Tasks

⚠️ Tasks to Complete:
  1. Web Application Firewall: Configure WAF plugin settings
  2. REST API Security: Install and configure plugin to disable REST API for non-authenticated users
📚 Reference Materials: For hotlinking protection and file modification restrictions, refer to the configuration steps documented during the first site installation. These steps were not repeated for the subdomain installation but should be applied using the same methodology.

Step 4Next Phase: Optimization

With the subdomain site secured, the next phase focuses on optimization to ensure maximum performance:

Optimization Roadmap
Post Revisions Management
↓
WordPress Memory Limit Configuration
↓
WP-Cron Optimization
↓
OPcache Configuration
↓
Caching Strategy (WP Super Cache / Redis)
↓
PHP-FPM Tuning
🎉 Installation Complete! Your subdomain WordPress site is now securely configured with hardened database privileges and file permissions. Proceed to the optimization phase to maximize site performance.

Best PracticesSecurity Maintenance Guidelines

Regular Security Audits

Permission Management Workflow

  1. Always run pre-update script before making administrative changes
  2. Perform necessary updates or modifications
  3. Immediately run post-update script after completion
  4. Verify changes with ls -l command
  5. Test site functionality thoroughly

Database Security Tips

🔒 Security Reminder: The principle of least privilege is not just a one-time configuration—it's an ongoing practice. Regularly review and audit permissions to ensure your site maintains its security posture over time.